﻿/*== Prc_DeleteLoadTestRun =============================================================================
* Updates the Prc_DeleteLoadTestRun SPROC so that it deletes data from the TSL tables whenever
* Visual Studio deletes data from the main DB tables
===============================================================================*/
	--START CODE--
	ALTER PROCEDURE Prc_DeleteLoadTestRun 
		@LoadTestRunId int
	AS
	BEGIN

		-- First delete the data collector log tables
		EXEC Prc_DeleteDataCollectorLogs @LoadTestRunId

		----------------------------------------------------------------
		-- TempTable to handle the tables to use for delete.
		-- rownum will insure we retrieve in the right order
		-- so foreign key constraints don't bite us
		DECLARE @Temp TABLE
			  (rownum int
			   , tableToUse nvarchar(50))

		-- Build out temp table, 
		INSERT INTO @temp VALUES(1, 'LoadTestFileAttachmentChunk')
		INSERT INTO @temp VALUES(2, 'LoadTestFileAttachment')
		INSERT INTO @temp VALUES(3, 'LoadTestDataCollectorLog')
		INSERT INTO @temp VALUES(4, 'LoadTestTestLog')
		INSERT INTO @temp VALUES(5, 'LoadTestBrowsers')
		INSERT INTO @temp VALUES(6, 'LoadTestNetworks')
		INSERT INTO @temp VALUES(7, 'LoadTestDetailMessage')
		INSERT INTO @temp VALUES(8, 'LoadTestTestDetail')
		INSERT INTO @temp VALUES(9, 'LoadTestPageDetail')
		INSERT INTO @temp VALUES(10, 'LoadTestTransactionDetail')
		INSERT INTO @temp VALUES(11, 'LoadTestTestSummaryData')
		INSERT INTO @temp VALUES(12, 'LoadTestTransactionSummaryData')
		INSERT INTO @temp VALUES(13, 'LoadTestPageSummaryData')
		INSERT INTO @temp VALUES(14, 'LoadTestPageSummaryByNetwork')
		INSERT INTO @temp VALUES(15, 'LoadTestCase')
		INSERT INTO @temp VALUES(16, 'LoadTestMessage')
		INSERT INTO @temp VALUES(17, 'LoadTestMessageType')
		INSERT INTO @temp VALUES(18, 'LoadTestThresholdMessage')
		INSERT INTO @temp VALUES(19, 'LoadTestPerformanceCounter')
		INSERT INTO @temp VALUES(20, 'LoadTestPerformanceCounterCategory')
		INSERT INTO @temp VALUES(21, 'LoadTestPerformanceCounterInstance')
		INSERT INTO @temp VALUES(22, 'LoadTestPerformanceCounterSample')
		INSERT INTO @temp VALUES(23, 'LoadTestRunAgent')
		INSERT INTO @temp VALUES(24, 'LoadTestRunInterval')
		INSERT INTO @temp VALUES(25, 'LoadTestScenario')
		INSERT INTO @temp VALUES(26, 'LoadTestSqlTrace')
		INSERT INTO @temp VALUES(27, 'WebLoadTestErrorDetail')
		INSERT INTO @temp VALUES(28, 'WebLoadTestTransaction')
		INSERT INTO @temp VALUES(29, 'WebLoadTestRequestMap')
		INSERT INTO @temp VALUES(30, 'LoadTestSystemUnderTestTag')
		INSERT INTO @temp VALUES(31, 'LoadTestSystemUnderTest')
		INSERT INTO @temp VALUES(32, 'LoadTestRun')

		----------------------------------------------------------------
		-- Added by the VSLT Reporting Tool
		INSERT INTO @temp VALUES(33, 'TSL_LoadTestCoreInfo')
		INSERT INTO @temp VALUES(34, 'TSL_ScoreCardMachineAvailableMB')
		INSERT INTO @temp VALUES(35, 'TSL_ScoreCardMachineCPU')
		----------------------------------------------------------------


		----------------------------------------------------------------
		-- Variables to control the behavior of the query
		DECLARE @iEndOfTable int
		DECLARE @iRowsToDelete int
		DECLARE @iRownum int
		DECLARE @tableName nvarchar(50)
		DECLARE @QueryToUse nvarchar(500)

		set @iEndOfTable = 0
		SET @iRowsToDelete = 10000
		SET @iRownum = 1


		WHILE @iRownum < 33
		BEGIN
			  SELECT @tableName = tableToUse FROM @TEMP WHERE rownum = @iRownum
			  WHILE @iEndOfTable = 0
			  BEGIN
					SET @QueryToUse = 'DELETE TOP(' + CAST(@iRowsToDelete AS nvarchar(10)) + ') FROM ' +
								@tableName + ' WHERE LoadTestRunId = ' +  CAST(@LoadTestRunId AS nvarchar(10))
					EXECUTE sp_executesql @QueryToUse
					IF (@@rowcount < @iRowsToDelete)
						  SET @iEndOfTable = 1
			  END
			  SET @iRownum = @iRownum + 1
			  SET @iEndOfTable = 0
		END

		----------------------------------------------------------------
		-- Added by the VSLT Reporting Tool
		WHILE @iRownum < 36
		BEGIN
			  SELECT @tableName = tableToUse FROM @TEMP WHERE rownum = @iRownum
			  SET @QueryToUse = 'DELETE FROM ' + @tableName + ' WHERE LoadTestRunId = ' 
					+  CAST(@LoadTestRunId AS nvarchar(10))
			  EXECUTE sp_executesql @QueryToUse
			  SET @iRownum = @iRownum + 1			
		END
		----------------------------------------------------------------
	
	END	
	GO
	--START CODE--
	GRANT EXECUTE ON Prc_DeleteLoadTestRun TO PUBLIC
	GO



/*== Prc_UpdateSummaryData3 =============================================================================
* Updates the Prc_UpdateSummaryData3 SPROC so that it adds the summary data from the main tables whenever
* Visual Studio finishes writing results to the DB at the end of a test run.
===============================================================================*/
	--START CODE--
	ALTER PROCEDURE Prc_UpdateSummaryData3 
		@LoadTestRunId int
		, @DeleteDetailTables bit
	AS
	BEGIN
		INSERT INTO LoadTestTestSummaryData 
			(LoadTestRunId, TestCaseId, TestsRun, Average, Minimum, Maximum, StandardDeviation)
		   SELECT LoadTestRunId, TestCaseId,
				  count(*) as TestsRun,
				  avg(ElapsedTime) as Average, 
				  min(ElapsedTime) as Minimum,
				  max(ElapsedTime) as Maximum,
				  ISNULL(STDEVP(ElapsedTime),0) AS StandardDeviation
			FROM  LoadTestTestDetail 
			WHERE LoadTestRunId=@LoadTestRunId
				  AND InMeasurementInterval = 1
			GROUP BY LoadTestRunId, TestCaseId

			EXEC Prc_UpdateTestSummary3 @LoadTestRunId


		INSERT INTO LoadTestTransactionSummaryData 
			(LoadTestRunId, TransactionId, TransactionCount, Average, Minimum, Maximum,AvgTransactionTime, StandardDeviation)
			SELECT LoadTestRunId, TransactionId,
				   count(*) as TransactionCount,
				   avg(ResponseTime) as Average, 
				   min(ResponseTime) as Minimum,
					   max(ResponseTime) as Maximum,
					   avg(ElapsedTime) as AverageTransactionTime,
					   ISNULL(STDEVP(ResponseTime),0) AS StandardDeviation
			FROM LoadTestTransactionDetail 
			WHERE  LoadTestRunId=@LoadTestRunId
				   AND InMeasurementInterval = 1
			GROUP BY LoadTestRunId, TransactionId

			EXEC Prc_UpdateTransactionSummary3 @LoadTestRunId


		INSERT INTO LoadTestPageSummaryData 
			(LoadTestRunId, PageId, PageCount, Average, Minimum, Maximum, StandardDeviation)
			SELECT LoadTestRunId, PageId,
				   count(*) as PageCount,
				   avg(ResponseTime) as Average, 
				   min(ResponseTime) as Minimum,
				   max(ResponseTime) as Maximum,
					   ISNULL(STDEVP(ResponseTime),0) AS StandardDeviation
			FROM   LoadTestPageDetail 
			WHERE  LoadTestRunId=@LoadTestRunId
				   AND InMeasurementInterval = 1
			GROUP BY LoadTestRunId, PageId

			EXEC Prc_UpdatePageSummary3 @LoadTestRunId


		INSERT INTO LoadTestPageSummaryByNetwork
			(LoadTestRunId, PageId, NetworkId, PageCount, Average, Minimum, Maximum, Goal, StandardDeviation)
			SELECT pageDetail.LoadTestRunId, pageDetail.PageId, testDetail.NetworkId,
				   count(*) as PageCount,
				   avg(ResponseTime) as Average, 
				   min(ResponseTime) as Minimum,
				   max(ResponseTime) as Maximum,
				   avg(ResponseTimeGoal) as Goal,
					   ISNULL(STDEVP(ResponseTime),0) AS StandardDeviation
			FROM   LoadTestPageDetail as pageDetail
			INNER JOIN LoadTestTestDetail as testDetail
			ON pageDetail.LoadTestRunId = testDetail.LoadTestRunId
				  AND pageDetail.TestDetailId = testDetail.TestDetailId
			WHERE pageDetail.LoadTestRunId = @LoadTestRunId
				  AND pageDetail.InMeasurementInterval = 1
			GROUP BY pageDetail.LoadTestRunId, PageId, testDetail.NetworkId
        
			EXEC Prc_UpdatePageSummaryByNetwork3 @LoadTestRunId

		IF @DeleteDetailTables = 1
		BEGIN
			DELETE from LoadTestTestDetail where LoadTestRunId = @LoadTestRunId
			DELETE from LoadTestTransactionDetail where LoadTestRunId = @LoadTestRunId
			DELETE from LoadTestPageDetail where LoadTestRunId = @LoadTestRunId
		END

			----------------------------------------------------------------
			-- Added by the VSLT Reporting Tool
			EXEC TSL_prc_UpdateLoadTestCoreInfoTable
			EXEC TSL_prc_UpdateScoreCardMachineAvailableMBTable
			EXEC TSL_prc_UpdateScoreCardMachineCpuTable
			----------------------------------------------------------------
	END
	GO
	--START CODE--
	GRANT EXECUTE ON Prc_DeleteLoadTestRun TO PUBLIC
	GO
